//
//  ReportsViewController.m
//  TimeTracker
//
//  Created by Michael Flakus on 2/25/13.
//  Copyright (c) 2013 Michael Flakus. All rights reserved.
//

#import "ReportsViewController.h"
#import "ReportsTableViewCell.h"

@interface ReportsViewController ()

@end

@implementation ReportsViewController

- (id)initWithStyle:(UITableViewStyle)style
{
    self = [super initWithStyle:style];
    if (self) {
        // Custom initialization
    }
    return self;
}

- (void)viewDidLoad
{
    [super viewDidLoad];
}

-(void)viewWillAppear:(BOOL)animated
{
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    if(_selectedStartTime == NULL)
    {
        _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
        _selectedRange = @"1D";
    }
    if(_selectedEndTime == NULL)
    {
        _selectedEndTime = [NSDate date];
    }
    
    [self loadReportData];
    [self.tableView reloadData];
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

#pragma mark - Table view data source

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView
{
    // Return the number of sections.
    return 1;
}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    // Return the number of rows in the section.
    if(_reportActivityNames.count == 0)
        return 1;
    else
        return _reportActivityNames.count;
}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    static NSString *CellIdentifier = @"ReportCell";
    ReportsTableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier forIndexPath:indexPath];
    
    int row = [indexPath row];
    
    NSLog(@"count: %d",_reportActivityNames.count);
    
    if(_reportActivityNames.count == 0)
    {
        cell.activityName.text = @"No data for this time period";
        cell.categoryName.text = @"";
        cell.totalTime.text = @"";
        cell.totalEntries.text = @"";
        cell.minTime.text = @"";
        cell.maxTime.text = @"";
        cell.avePerEntry.text = @"";
        //cell.avePerDay.text = @"";
        cell.totalTimeLabel.hidden = YES;
        cell.totalEntriesLabel.hidden = YES;
        cell.minTimeLabel.hidden = YES;
        cell.maxTimeLabel.hidden = YES;
        cell.avePerEntryLabel.hidden = YES;
        //cell.avePerDayLabel.hidden = YES;
    }
    else
    {
        cell.activityName.text = _reportActivityNames[row];
        cell.categoryName.text = _reportCategoryNames[row];
        cell.totalTimeLabel.hidden = NO;
        cell.totalEntriesLabel.hidden = NO;
        cell.minTimeLabel.hidden = NO;
        cell.maxTimeLabel.hidden = NO;
        cell.avePerEntryLabel.hidden = NO;
        //cell.avePerDayLabel.hidden = NO;
        
        // calculate the total time in hours/minutes from seconds
        int hours = 0;
        int minutes = 0;
        int seconds = 0;
        
        hours = [_reportTotalTimes[row] floatValue] / 3600;
        minutes = (([_reportTotalTimes[row] floatValue] - (hours * 3600)) / 60);
        seconds = ([_reportTotalTimes[row] floatValue] - (hours * 3600) - (minutes * 60));
        
        NSString *minutes_str = @"";
        // pad to two digits
        if(minutes < 10)
            minutes_str = [NSString stringWithFormat:@"0%d",minutes];
        else
            minutes_str = [NSString stringWithFormat:@"%d",minutes];
        
        NSString *seconds_str = @"";
        // pad to two digits
        if(seconds < 10)
            seconds_str = [NSString stringWithFormat:@"0%d",seconds];
        else
            seconds_str = [NSString stringWithFormat:@"%d",seconds];
        
        if(hours == 0)
        {
            if(minutes == 0)
                cell.totalTime.text = [NSString stringWithFormat:@"%@s",seconds_str];
            else
                cell.totalTime.text = [NSString stringWithFormat:@"%@m %@s",minutes_str,seconds_str];
        }
        else
            cell.totalTime.text = [NSString stringWithFormat:@"%dh %@m %@s",hours,minutes_str,seconds_str];

        
        // calculate the min time in hours/minutes from seconds
        hours = 0;
        minutes = 0;
        seconds = 0;
        
        NSLog(@"min time in table cell: %@",_reportMinTimes[row]);
        
        hours = [_reportMinTimes[row] floatValue] / 3600;
        minutes = round(([_reportMinTimes[row] floatValue] - (hours * 3600)) / 60);
        
        minutes_str = @"";
        // pad to two digits
        if(minutes < 10)
            minutes_str = [NSString stringWithFormat:@"0%d",minutes];
        else
            minutes_str = [NSString stringWithFormat:@"%d",minutes];
        
        if(hours == 0)
            cell.minTime.text = [NSString stringWithFormat:@"%@m",minutes_str];
        else
            cell.minTime.text = [NSString stringWithFormat:@"%dh %@m",hours,minutes_str];

        // calculate the max time in hours/minutes from seconds
        hours = 0;
        minutes = 0;
        seconds = 0;
        
        hours = [_reportMaxTimes[row] floatValue] / 3600;
        minutes = round(([_reportMaxTimes[row] floatValue] - (hours * 3600)) / 60);
        
        minutes_str = @"";
        // pad to two digits
        if(minutes < 10)
            minutes_str = [NSString stringWithFormat:@"0%d",minutes];
        else
            minutes_str = [NSString stringWithFormat:@"%d",minutes];
        
        if(hours == 0)
            cell.maxTime.text = [NSString stringWithFormat:@"%@m",minutes_str];
        else
            cell.maxTime.text = [NSString stringWithFormat:@"%dh %@m",hours,minutes_str];
        
        
        // total entries        
        cell.totalEntries.text = [NSString stringWithFormat:@"%d",[_reportEntries[row] intValue]];
        
        
        // calculate the ave time per entry in hours/minutes from seconds
        hours = 0;
        minutes = 0;
        seconds = 0;
        float secondsPerEntry = [_reportTotalTimes[row] floatValue] / [_reportEntries[row] intValue];
        hours = secondsPerEntry / 3600;
        minutes = round((secondsPerEntry - (hours * 3600)) / 60);
        
        minutes_str = @"";
        // pad to two digits
        if(minutes < 10)
            minutes_str = [NSString stringWithFormat:@"0%d",minutes];
        else
            minutes_str = [NSString stringWithFormat:@"%d",minutes];
        
        if(hours == 0)
            cell.avePerEntry.text = [NSString stringWithFormat:@"%@m",minutes_str];
        else
            cell.avePerEntry.text = [NSString stringWithFormat:@"%dh %@m",hours,minutes_str];
        
        
        // calculate the ave/day
        /*
        NSCalendar *gregorianCalendar = [[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar];
        NSDateComponents *components = [gregorianCalendar components:NSDayCalendarUnit fromDate:_selectedStartTime toDate:_selectedEndTime options:0];
        int days = [components day];
        NSLog(@"days: %d",days);
        hours = 0;
        minutes = 0;
        seconds = 0;
        float secondsPerDay = [_reportTotalTimes[row] floatValue] / (days+1);
        NSLog(@"seconds per day: %f",secondsPerDay);
        hours = secondsPerDay / 3600;
        minutes = round((secondsPerDay - (hours * 3600)) / 60);
        
        minutes_str = @"";
        // pad to two digits
        if(minutes < 10)
            minutes_str = [NSString stringWithFormat:@"0%d",minutes];
        else
            minutes_str = [NSString stringWithFormat:@"%d",minutes];
        
        cell.avePerDay.text = [NSString stringWithFormat:@"%dh %@m",hours,minutes_str];
        */

        
    }
    return cell;
}

- (UIView *)tableView:(UITableView *)tableView viewForHeaderInSection:(NSInteger)section
{
	// create the parent view that will hold header Label
	UIView* customView = [[UIView alloc] initWithFrame:CGRectMake(10.0, 0.0, 300.0, 44.0)];
    customView.backgroundColor = [UIColor blackColor];
    
    if(_headerLabel == NULL)
    {
        _headerLabel = [[UILabel alloc] init];
        _headerLabel.frame = CGRectMake(0.0, 4.0, 320.0, 28.0);
        _headerLabel.text = @"TimeTracker Report: Today";
        _headerLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:18.0];
        _headerLabel.textAlignment = UITextAlignmentCenter;
        _headerLabel.backgroundColor = [UIColor blackColor];
        _headerLabel.textColor = [UIColor whiteColor];
    }
    [customView addSubview:_headerLabel];
    
    NSLog(@"selected range: %@",_selectedRange);

    // create report date range buttons
    
    // TODO: buttons only need to be initialized once, then just added to view after that (see headerLabel above)
    
    // 1D
    _oneDayButton = [[UIButton alloc] initWithFrame:CGRectZero];
    _oneDayButton.frame = CGRectMake(20.0, 34.0, 36.0, 28.0);
    if([_selectedRange isEqualToString:@"1D"])
        _oneDayButton.backgroundColor = [UIColor blueColor];
    else
        _oneDayButton.backgroundColor = [UIColor blackColor];
    [_oneDayButton setTitleColor:[UIColor whiteColor] forState:UIControlStateNormal];
    _oneDayButton.titleLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:13.0];
    [_oneDayButton setTitle:@"1D" forState:UIControlStateNormal];
    [_oneDayButton addTarget: self action: @selector(oneDayClicked:) forControlEvents: UIControlEventTouchDown];
    [customView addSubview:_oneDayButton];
    
    
    // 1W
    _oneWeekButton = [[UIButton alloc] initWithFrame:CGRectZero];
    _oneWeekButton.frame = CGRectMake(60.0, 34.0, 36.0, 28.0);
    if([_selectedRange isEqualToString:@"1W"])
        _oneWeekButton.backgroundColor = [UIColor blueColor];
    else
        _oneWeekButton.backgroundColor = [UIColor blackColor];
    [_oneWeekButton setTitleColor:[UIColor whiteColor] forState:UIControlStateNormal];
    _oneWeekButton.titleLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:13.0];
    [_oneWeekButton setTitle:@"1W" forState:UIControlStateNormal];
    [_oneWeekButton addTarget: self action: @selector(oneWeekClicked:) forControlEvents: UIControlEventTouchDown];
    [customView addSubview:_oneWeekButton];
    
    
    // 1M
    _oneMonthButton = [[UIButton alloc] initWithFrame:CGRectZero];
    _oneMonthButton.frame = CGRectMake(100.0, 34.0, 36.0, 28.0);
    if([_selectedRange isEqualToString:@"1M"])
        _oneMonthButton.backgroundColor = [UIColor blueColor];
    else
        _oneMonthButton.backgroundColor = [UIColor blackColor];
    [_oneMonthButton setTitleColor:[UIColor whiteColor] forState:UIControlStateNormal];
    _oneMonthButton.titleLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:13.0];
    [_oneMonthButton setTitle:@"1M" forState:UIControlStateNormal];
    [_oneMonthButton addTarget: self action: @selector(oneMonthClicked:) forControlEvents: UIControlEventTouchDown];
    [customView addSubview:_oneMonthButton];
    
    
    // 3M
    _threeMonthsButton = [[UIButton alloc] initWithFrame:CGRectZero];
    _threeMonthsButton.frame = CGRectMake(140.0, 34.0, 36.0, 28.0);
    if([_selectedRange isEqualToString:@"3M"])
        _threeMonthsButton.backgroundColor = [UIColor blueColor];
    else
        _threeMonthsButton.backgroundColor = [UIColor blackColor];
    [_threeMonthsButton setTitleColor:[UIColor whiteColor] forState:UIControlStateNormal];
    _threeMonthsButton.titleLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:13.0];
    [_threeMonthsButton setTitle:@"3M" forState:UIControlStateNormal];
    [_threeMonthsButton addTarget: self action: @selector(threeMonthsClicked:) forControlEvents: UIControlEventTouchDown];
    [customView addSubview:_threeMonthsButton];
    
    // 6M
    _sixMonthsButton = [[UIButton alloc] initWithFrame:CGRectZero];
    _sixMonthsButton.frame = CGRectMake(180.0, 34.0, 36.0, 28.0);
    if([_selectedRange isEqualToString:@"6M"])
        _sixMonthsButton.backgroundColor = [UIColor blueColor];
    else
        _sixMonthsButton.backgroundColor = [UIColor blackColor];
    [_sixMonthsButton setTitleColor:[UIColor whiteColor] forState:UIControlStateNormal];
    _sixMonthsButton.titleLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:13.0];
    [_sixMonthsButton setTitle:@"6M" forState:UIControlStateNormal];
    [_sixMonthsButton addTarget: self action: @selector(sixMonthsClicked:) forControlEvents: UIControlEventTouchDown];
    [customView addSubview:_sixMonthsButton];
    
    // 1Y
    _oneYearButton = [[UIButton alloc] initWithFrame:CGRectZero];
    _oneYearButton.frame = CGRectMake(220.0, 34.0, 36.0, 28.0);
    if([_selectedRange isEqualToString:@"1Y"])
        _oneYearButton.backgroundColor = [UIColor blueColor];
    else
        _oneYearButton.backgroundColor = [UIColor blackColor];
    [_oneYearButton setTitleColor:[UIColor whiteColor] forState:UIControlStateNormal];
    _oneYearButton.titleLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:13.0];
    [_oneYearButton setTitle:@"1Y" forState:UIControlStateNormal];
    [_oneYearButton addTarget: self action: @selector(oneYearClicked:) forControlEvents: UIControlEventTouchDown];
    [customView addSubview:_oneYearButton];
    
    // 2Y
    _twoYearsButton = [[UIButton alloc] initWithFrame:CGRectZero];
    _twoYearsButton.frame = CGRectMake(260.0, 34.0, 36.0, 28.0);
    if([_selectedRange isEqualToString:@"2Y"])
        _twoYearsButton.backgroundColor = [UIColor blueColor];
    else
        _twoYearsButton.backgroundColor = [UIColor blackColor];
    [_twoYearsButton setTitleColor:[UIColor whiteColor] forState:UIControlStateNormal];
    _twoYearsButton.titleLabel.font = [UIFont fontWithName:@"Helvetica-Bold" size:13.0];
    [_twoYearsButton setTitle:@"2Y" forState:UIControlStateNormal];
    [_twoYearsButton addTarget: self action: @selector(twoYearsClicked:) forControlEvents: UIControlEventTouchDown];
    [customView addSubview:_twoYearsButton];
    
    
	return customView;
}

- (CGFloat) tableView:(UITableView *)tableView heightForHeaderInSection:(NSInteger)section
{
	return 68.0;
}



- (void) oneDayClicked: (id)sender
{
    NSLog(@"oneDayClicked");
    _selectedRange = @"1D";
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
    
    _headerLabel.text = @"TimeTracker Report: Today";
    
    [self loadReportData];
}

- (void) oneWeekClicked: (id)sender
{
    NSLog(@"oneWeekClicked");
    _selectedRange = @"1W";
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
    
    // subtract 1 week
    NSDateComponents *dayComponent = [[NSDateComponents alloc] init];
    dayComponent.week = -1;
    
    NSCalendar *theCalendar = [NSCalendar currentCalendar];
    _selectedStartTime = [theCalendar dateByAddingComponents:dayComponent toDate:_selectedStartTime options:0];
    
    _headerLabel.text = @"TimeTracker Report: 1 Week";
    
    [self loadReportData];
}

- (void) oneMonthClicked: (id)sender
{
    NSLog(@"oneMonthClicked");
    _selectedRange = @"1M";
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
    
    // subtract 1 month
    NSDateComponents *dayComponent = [[NSDateComponents alloc] init];
    dayComponent.month = -1;
    
    NSCalendar *theCalendar = [NSCalendar currentCalendar];
    _selectedStartTime = [theCalendar dateByAddingComponents:dayComponent toDate:_selectedStartTime options:0];
    
    _headerLabel.text = @"TimeTracker Report: 1 Month";
    
    [self loadReportData];
}

- (void) threeMonthsClicked: (id)sender
{
    NSLog(@"threeMonthsClicked");
    _selectedRange = @"3M";
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
    
    // subtract 3 month
    NSDateComponents *dayComponent = [[NSDateComponents alloc] init];
    dayComponent.month = -3;
    
    NSCalendar *theCalendar = [NSCalendar currentCalendar];
    _selectedStartTime = [theCalendar dateByAddingComponents:dayComponent toDate:_selectedStartTime options:0];
    
    _headerLabel.text = @"TimeTracker Report: 3 Months";
    
    [self loadReportData];
}


- (void) sixMonthsClicked: (id)sender
{
    NSLog(@"sixMonthsClicked");
    _selectedRange = @"6M";
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
    
    // subtract 6 months
    NSDateComponents *dayComponent = [[NSDateComponents alloc] init];
    dayComponent.month = -6;
    
    NSCalendar *theCalendar = [NSCalendar currentCalendar];
    _selectedStartTime = [theCalendar dateByAddingComponents:dayComponent toDate:_selectedStartTime options:0];
    
    _headerLabel.text = @"TimeTracker Report: 6 Months";
    
    [self loadReportData];
}


- (void) oneYearClicked: (id)sender
{
    NSLog(@"oneYearClicked");
    _selectedRange = @"1Y";
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
    
    // subtract 1 year
    NSDateComponents *dayComponent = [[NSDateComponents alloc] init];
    dayComponent.year = -1;
    
    NSCalendar *theCalendar = [NSCalendar currentCalendar];
    _selectedStartTime = [theCalendar dateByAddingComponents:dayComponent toDate:_selectedStartTime options:0];
    
    _headerLabel.text = @"TimeTracker Report: 1 Year";
    
    [self loadReportData];
}


- (void) twoYearsClicked: (id)sender
{
    NSLog(@"twoYearsClicked");
    _selectedRange = @"2Y";
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    NSDateFormatter *dF = [[NSDateFormatter alloc] init];
    [dF setDateFormat:@"yyyy-MM-dd"];
    
    _selectedStartTime = [dateFormatDB dateFromString:[NSString stringWithFormat:@"%@ 00:00:00",[dF stringFromDate:[NSDate date]]]];
    
    // subtract 2 years
    NSDateComponents *dayComponent = [[NSDateComponents alloc] init];
    dayComponent.year = -2;
    
    NSCalendar *theCalendar = [NSCalendar currentCalendar];
    _selectedStartTime = [theCalendar dateByAddingComponents:dayComponent toDate:_selectedStartTime options:0];
    
    _headerLabel.text = @"TimeTracker Report: 2 Years";
    
    [self loadReportData];
}

#pragma mark - Table view delegate

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath
{
    // Navigation logic may go here. Create and push another view controller.
    /*
     <#DetailViewController#> *detailViewController = [[<#DetailViewController#> alloc] initWithNibName:@"<#Nib name#>" bundle:nil];
     // ...
     // Pass the selected object to the new view controller.
     [self.navigationController pushViewController:detailViewController animated:YES];
     */
}



- (void)loadReportData
{
    
    NSLog(@"loadReportData...");
    
    // load data from database
    _reportActivityNames = [[NSMutableArray alloc] init];
    _reportCategoryNames = [[NSMutableArray alloc] init];
    _reportTotalTimes = [[NSMutableArray alloc] init];
    _reportMinTimes = [[NSMutableArray alloc] init];
    _reportMaxTimes = [[NSMutableArray alloc] init];
    _reportEntries = [[NSMutableArray alloc] init];
    
    
    NSDateFormatter *dateFormatDB = [[NSDateFormatter alloc] init];
    [dateFormatDB setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    
    // database lookups
    @try {
        
        // connect to db
        
        NSString *docsDir;
        NSArray *dirPaths;
        
        // Get the documents directory
        dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        
        docsDir = dirPaths[0];
        
        // Build the path to the database file
        _databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"TimeTracker.sqlite"]];
        
        NSFileManager *filemgr = [NSFileManager defaultManager];
        
        // check to see if database already exists...
        if ([filemgr fileExistsAtPath: _databasePath ] == YES)
        {
            // open database
            if(!(sqlite3_open([_databasePath UTF8String], &_database) == SQLITE_OK))
            {
                NSLog(@"An error has occured when trying to open the database");
            }
            
            // select all activity ids that have any activity during the time period
            NSString *select_unique_activities = [NSString stringWithFormat:@"select distinct activities.activity_name,categories.name from tasklog,activities,categories where tasklog.activity_id = activities.id AND activities.category_id = categories.id AND tasklog.start_time >= '%@' AND tasklog.active = 1 order by activities.activity_name",[dateFormatDB stringFromDate:_selectedStartTime]];
            const char *select_unique_activities_const = [select_unique_activities UTF8String];
            
            NSLog(@"query: %s",select_unique_activities_const);
            
            sqlite3_stmt *res_unique_activities;
            if(sqlite3_prepare(_database, select_unique_activities_const, -1, &res_unique_activities, NULL) != SQLITE_OK)
            {
                NSLog(@"Problem with prepare statement: %s",select_unique_activities_const);
                NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(_database), sqlite3_errcode(_database));
            }
            else
            {
                while (sqlite3_step(res_unique_activities)==SQLITE_ROW)
                {
                    [_reportActivityNames addObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(res_unique_activities,0)]];
                    [_reportCategoryNames addObject:[NSString stringWithUTF8String:(char *) sqlite3_column_text(res_unique_activities,1)]];
                }
                sqlite3_finalize(res_unique_activities);
            }
            
            NSString *select_tasks;
            const char *select_tasks_const;
            sqlite3_stmt *res_tasks;
            NSString *startTimeString;
            NSString *endTimeString;
            NSDate *startTime;
            NSDate *endTime;
            int totalSecondsTracked = 0;
            int minTimeSeconds;
            int maxTimeSeconds;
            int entries;
            
            // for each activity, select all recorded tasks
            for(int i=0;i<_reportActivityNames.count;i++)
            {
                minTimeSeconds = 999999999;
                maxTimeSeconds = 0;
                entries = 0;
                
                select_tasks = [NSString stringWithFormat:@"select tasklog.start_time, tasklog.end_time from  tasklog,activities where tasklog.activity_id = activities.id  AND activities.activity_name = '%@' AND tasklog.start_time >= '%@' AND tasklog.active = 1",_reportActivityNames[i],[dateFormatDB stringFromDate:_selectedStartTime]];
                select_tasks_const = [select_tasks UTF8String];
                NSLog(@"report query: %s",select_tasks_const);
                if(sqlite3_prepare(_database, select_tasks_const, -1, &res_tasks, NULL) != SQLITE_OK)
                {
                    NSLog(@"Problem with prepare statement: %s",select_tasks_const);
                    NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(_database), sqlite3_errcode(_database));
                }
                else
                {
                    totalSecondsTracked = 0;
                    while (sqlite3_step(res_tasks)==SQLITE_ROW) {
                        // for each task, calculate the total time in seconds for the task (even the end time is null, then use the current time for the end time
                        startTimeString = [NSString stringWithUTF8String:(char *) sqlite3_column_text(res_tasks,0)];
                        startTime = [dateFormatDB dateFromString:startTimeString];
                        if(sqlite3_column_text(res_tasks,1) != NULL)
                        {
                            endTimeString = [NSString stringWithUTF8String:(char *) sqlite3_column_text(res_tasks,1)];
                            NSLog(@"end time string is not null: %@",endTimeString);
                            endTime = [dateFormatDB dateFromString:endTimeString];
                        }
                        else
                            endTime = [NSDate date];
                        
                        NSTimeInterval secondsTracked = [endTime timeIntervalSinceDate: startTime];
                        NSLog(@"seconds tracked for task: %f",secondsTracked);
                        
                        if(secondsTracked < minTimeSeconds)
                        {
                            // new minimum length
                            NSLog(@"new min: %f",secondsTracked);
                            minTimeSeconds = secondsTracked;
                        }
                        
                        if(secondsTracked > maxTimeSeconds)
                        {
                            // new max length
                            maxTimeSeconds = secondsTracked;
                        }
                        
                        entries++;
                        
                        totalSecondsTracked += secondsTracked;
                    }
                    sqlite3_finalize(res_tasks);
                }
                
                [_reportTotalTimes addObject:[NSString stringWithFormat:@"%i", totalSecondsTracked]];
                [_reportMinTimes addObject:[NSString stringWithFormat:@"%i", minTimeSeconds]];
                [_reportMaxTimes addObject:[NSString stringWithFormat:@"%i", maxTimeSeconds]];
                [_reportEntries addObject:[NSString stringWithFormat:@"%i", entries]];
                
            }
            
            sqlite3_close(_database);
        }
        else {
            NSLog(@"Error: database could not be found in ReportsViewController");
        }
    }
    @catch (NSException *exception) {
        NSLog(@"An exception occured: %@", [exception reason]);
    }
    @finally {
        // close the database connection
        sqlite3_close(_database);
    }
    
    //reload table with updated data
    [self.tableView reloadData];
}



@end
